﻿
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'RethrowError')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')
END
GO

ALTER PROCEDURE RethrowError AS
    /* Return if there is no error information to retrieve. */
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200); 

    /* Assign variables to error-handling functions that
       capture information for RAISERROR. */

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); 

    /* Building the message string that will contain original
       error information. */

    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
         'Message: '+ ERROR_MESSAGE(); 

    /* Raise an error: msg_str parameter of RAISERROR will contain
	   the original error information. */

    RAISERROR(@ErrorMessage, @ErrorSeverity, 1,
        @ErrorNumber,    /* parameter: original error number. */
        @ErrorSeverity,  /* parameter: original error severity. */
        @ErrorState,     /* parameter: original error state. */
        @ErrorProcedure, /* parameter: original error procedure name. */
        @ErrorLine       /* parameter: original error line number. */
        );

GO

----------------------------------------------------------------
-- [dbo].[dnc_Zone] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_Zone')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_Zone] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_Zone]
    @iD int OUT,
	@name varchar(255),
	@zoneImageId int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_Zone] ([Name], [ZoneImageId])
	VALUES (@name, @zoneImageId)
    SET @iD = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Updatednc_Zone')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Updatednc_Zone] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Updatednc_Zone]
    @iD int,
	@name varchar(255),
	@zoneImageId int
AS
BEGIN

	--The [dbo].[dnc_Zone] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[dnc_Zone] 
	SET [Name] = @name, [ZoneImageId] = @zoneImageId
	WHERE [ID]=@iD

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Update aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Deletednc_Zone')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Deletednc_Zone] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Deletednc_Zone]
	 @iD int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[dnc_Zone]
	WHERE [ID]=@iD
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_Zone')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_Zone] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_Zone]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_Zone].[ID] AS 'ID',
	[dnc_Zone].[Name] AS 'Name',
	[dnc_Zone].[ZoneImageId] AS 'ZoneImageId'
FROM [dbo].[dnc_Zone] [dnc_Zone]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_ZoneByID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_ZoneByID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_ZoneByID] 
	@iD int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Zone].[ID] AS 'ID',
	[dnc_Zone].[Name] AS 'Name',
	[dnc_Zone].[ZoneImageId] AS 'ZoneImageId'
	FROM [dbo].[dnc_Zone] [dnc_Zone]
	WHERE [ID]=@iD

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_ZoneByZoneImageId')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_ZoneByZoneImageId] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_ZoneByZoneImageId] 
	@zoneImageId int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Zone].[ID] AS 'ID',
	[dnc_Zone].[Name] AS 'Name',
	[dnc_Zone].[ZoneImageId] AS 'ZoneImageId'
	FROM [dbo].[dnc_Zone] [dnc_Zone]
	WHERE [ZoneImageId]=@zoneImageId

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[dnc_ZoneImage] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_ZoneImage')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_ZoneImage] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_ZoneImage]
    @id int OUT,
	@imageHeight decimal(5,2),
	@imageWidth decimal(5,2),
	@name varchar(100)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_ZoneImage] ([ImageHeight], [ImageWidth], [Name])
	VALUES (@imageHeight, @imageWidth, @name)
    SET @id = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Updatednc_ZoneImage')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Updatednc_ZoneImage] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Updatednc_ZoneImage]
    @id int,
	@imageHeight decimal(5,2),
	@imageWidth decimal(5,2),
	@name varchar(100)
AS
BEGIN

	--The [dbo].[dnc_ZoneImage] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[dnc_ZoneImage] 
	SET [ImageHeight] = @imageHeight, [ImageWidth] = @imageWidth, [Name] = @name
	WHERE [Id]=@id

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Update aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Deletednc_ZoneImage')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Deletednc_ZoneImage] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Deletednc_ZoneImage]
	 @id int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[dnc_ZoneImage]
	WHERE [Id]=@id
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_ZoneImage')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_ZoneImage] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_ZoneImage]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_ZoneImage].[Id] AS 'Id',
	[dnc_ZoneImage].[ImageHeight] AS 'ImageHeight',
	[dnc_ZoneImage].[ImageWidth] AS 'ImageWidth',
	[dnc_ZoneImage].[Name] AS 'Name'
FROM [dbo].[dnc_ZoneImage] [dnc_ZoneImage]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_ZoneImageById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_ZoneImageById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_ZoneImageById] 
	@id int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_ZoneImage].[Id] AS 'Id',
	[dnc_ZoneImage].[ImageHeight] AS 'ImageHeight',
	[dnc_ZoneImage].[ImageWidth] AS 'ImageWidth',
	[dnc_ZoneImage].[Name] AS 'Name'
	FROM [dbo].[dnc_ZoneImage] [dnc_ZoneImage]
	WHERE [Id]=@id

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[dnc_ZoneImage] table doesn't have at least one foreign key column or the foreign keys are also primary keys.


